﻿Option Strict On
Imports SCS.BusinessEntities
Imports System.Collections.Generic
Imports System.Data.SqlClient
Imports System.Configuration
Public Class AlumnosRepositorio
    Implements IAlumnosRepositorio


    Public Sub Add(ByVal item As Alumno) Implements IAlumnosRepositorio.Add
        Dim con As SqlConnection = Nothing
        Try
            con = New SqlConnection()
            con.ConnectionString = _
                ConfigurationManager.ConnectionStrings("CursoDB").ConnectionString
            Dim cmd As New SqlCommand
            cmd.Connection = con
            cmd.CommandText = "usp_alumnos_insertar"
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@id", item.id)
            cmd.Parameters.AddWithValue("@nombre", item.nombre)
            cmd.Parameters.AddWithValue("@edad", item.edad)
            con.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            Throw
        Finally
            'seha que se produsca error o  no  (siempre)
            If (con IsNot Nothing) Then
                con.Close()

            End If
        End Try
    End Sub

    Public Sub Delete(ByVal item As Alumno) Implements IAlumnosRepositorio.Delete
        Dim con As SqlConnection = Nothing
        Try
            con = New SqlConnection()
            con.ConnectionString = ConfigurationManager.ConnectionStrings("CursoDB").ConnectionString
            Dim cmd As New SqlCommand
            cmd.Connection = con
            cmd.CommandText = "usp_alumnos_delete"
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@id", item.id)
            con.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        Finally
            'seha que se produsca error o  no  (siempre)
            If (con IsNot Nothing) Then
                con.Close()

            End If
        End Try
    End Sub

    Public Function GetAll() As List(Of Alumno) Implements IAlumnosRepositorio.GetAll
        Dim con As SqlConnection = Nothing
        Dim result = New List(Of Alumno)
        Try
            con = New SqlConnection()
            con.ConnectionString = ConfigurationManager.ConnectionStrings("CursoDB").ConnectionString
            Dim cmd As New SqlCommand
            cmd.Connection = con
            cmd.CommandText = "usp_alumnos_getall"
            cmd.CommandType = CommandType.StoredProcedure

            con.Open()
            Dim reader = cmd.ExecuteReader()
            Dim idindex As Integer = reader.GetOrdinal("Id")
            Dim nombreindex As Integer = reader.GetOrdinal("nombre")
            Dim edadindex As Integer = reader.GetOrdinal("edad")
            While reader.Read
                Dim ent As New Alumno
                ent.id = reader.GetGuid(idindex)
                ent.nombre = reader.GetString(nombreindex)
                If (Not reader.IsDBNull(edadindex)) Then
                    ent.edad = reader.GetInt32(edadindex)
                End If
                result.Add(ent)
            End While
            reader.Close()

        Catch ex As Exception
            Throw
        Finally
            'seha que se produsca error o  no  (siempre)
            If (con IsNot Nothing) Then
                con.Close()
            End If
        End Try



        Return result
    End Function

    Public Function GetOne(ByVal id As System.Guid) As Alumno Implements IAlumnosRepositorio.GetOne
        Dim result As Alumno = Nothing
        Dim con As SqlConnection = Nothing
        Dim reader As SqlDataReader = Nothing
        Try
            con = New SqlConnection()

            con.ConnectionString = ConfigurationManager.ConnectionStrings("CursoDB").ConnectionString
            Dim cmd As New SqlCommand
            cmd.Connection = con
            cmd.CommandText = "usp_alumnos_getone"
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@id", id)
            con.Open()
            reader = cmd.ExecuteReader(System.Data.CommandBehavior.SingleResult Or System.Data.CommandBehavior.SingleRow)
            Dim idindex = reader.GetOrdinal("id")
            Dim nombreindex = reader.GetOrdinal("nombre")
            Dim edadindex = reader.GetOrdinal("edad")
            If (reader.Read) Then
                result = New Alumno
                result.id = reader.GetGuid(idindex)
                result.nombre = reader.GetString(nombreindex)
                If (Not reader.IsDBNull(edadindex)) Then
                    result.edad = reader.GetInt32(edadindex)
                End If
            End If
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        Finally
            'seha que se produsca error o  no  (siempre)
            If (reader IsNot Nothing) Then
                reader.Close()
            End If
            If (con IsNot Nothing) Then
                con.Close()
            End If
        End Try
        Return result
    End Function

    Public Sub Update(ByVal item As Alumno) Implements IAlumnosRepositorio.Update
        Dim con As SqlConnection = Nothing
        Try
            con = New SqlConnection()
            con.ConnectionString = ConfigurationManager.ConnectionStrings("CursoDB").ConnectionString
            Dim cmd As New SqlCommand
            cmd.Connection = con
            cmd.CommandText = "usp_alumnos_update"
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@id", item.id)
            cmd.Parameters.AddWithValue("@nombre", item.nombre)
            cmd.Parameters.AddWithValue("@edad", item.edad)
            con.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            Throw
        Finally
            'seha que se produsca error o  no  (siempre)
            If (con IsNot Nothing) Then
                con.Close()

            End If
        End Try
    End Sub

    Public Function GetData() As System.Data.DataTable Implements IAlumnosRepositorio.GetData
        Dim cmd As SqlCommand = Nothing
        Dim con As SqlConnection = Nothing
        Dim result = New DataTable("Alumnos")
        Try
            con = New SqlConnection()
            con.ConnectionString = ConfigurationManager.ConnectionStrings("CursoDB").ConnectionString
            cmd = New SqlCommand("usp_alumnos_getall", con)
            cmd.CommandType = CommandType.StoredProcedure
            Dim adp = New SqlDataAdapter(cmd)
            adp.Fill(result)
        Catch ex As Exception
            Throw
        Finally
            If (con IsNot Nothing) Then
                con.Close()
            End If
        End Try
        Return result
    End Function

    Public Sub SaveData(ByVal dt As System.Data.DataTable) Implements IAlumnosRepositorio.SaveData
        Dim Connection As SqlConnection = Nothing
        Dim Tran As SqlTransaction = Nothing

        Try

            Connection = New SqlConnection()
            Connection.ConnectionString = ConfigurationManager.ConnectionStrings("CursoDB").ConnectionString
            Dim SelectCommand = New SqlCommand("usp_alumnos_getall", Connection)
            SelectCommand.CommandType = CommandType.StoredProcedure

            Dim InsertCommand = New SqlCommand("usp_alumnos_insert", Connection)
            InsertCommand.CommandType = CommandType.StoredProcedure
            InsertCommand.Parameters.Add("@id", SqlDbType.UniqueIdentifier, 0, "Id")
            InsertCommand.Parameters.Add("@nombre", SqlDbType.VarChar, 50, "Nombre")
            InsertCommand.Parameters.Add("@edad", SqlDbType.Int, 0, "Edad")

            Dim UpdateCommand = New SqlCommand("usp_alumnos_update", Connection)
            UpdateCommand.CommandType = CommandType.StoredProcedure
            UpdateCommand.Parameters.Add("@id", SqlDbType.UniqueIdentifier, 0, "Id")
            UpdateCommand.Parameters.Add("@nombre", SqlDbType.VarChar, 500, "Nombre")
            UpdateCommand.Parameters.Add("@edad", SqlDbType.Int, 0, "Edad")


            Dim DeleteCommand = New SqlCommand("usp_alumnos_delete", Connection)
            DeleteCommand.CommandType = CommandType.StoredProcedure
            DeleteCommand.Parameters.Add("@id", SqlDbType.UniqueIdentifier, 0, "Id")


            Dim adp = New SqlDataAdapter(SelectCommand)
            adp.AcceptChangesDuringUpdate = True
            adp.InsertCommand = InsertCommand
            adp.UpdateCommand = UpdateCommand
            adp.DeleteCommand = DeleteCommand


            Connection.Open()

            Tran = Connection.BeginTransaction()

            adp.InsertCommand.Transaction = Tran
            adp.UpdateCommand.Transaction = Tran
            adp.DeleteCommand.Transaction = Tran

            adp.UpdateBatchSize = 20

            adp.Update(dt)

            Tran.Commit()

           Catch ex As Exception
            Throw
        Finally
            If (Connection IsNot Nothing) Then
                Connection.Close()
            End If

        End Try
            
    End Sub
End Class
